package com.excelAnalysis;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @BelongsProject: study
 * @BelongsPackage: com.excelAnalysis
 * @Author: zhangzhanheng
 * @CreateTime: 2024-05-02  10:08
 * @Description: TODO
 * @Version: 1.0
 */
public class processMain {
    public static void main(String[] args) {
        try {
            // Excel文件路径
            String filePath = "C:\\Users\\13569\\Desktop\\process.xlsx";

            // 创建一个 FileInputStream 对象来读取文件
            FileInputStream inputStream = new FileInputStream(new File(filePath));

            // 创建一个 XSSFWorkbook 对象来处理Excel文件
            Workbook workbook = new XSSFWorkbook(inputStream);

            // 获取第一个表单（Sheet）
            Sheet sheet = workbook.getSheetAt(0);
            List<ExcelModel> list = new ArrayList<>();
            // 遍历每一行
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                // 遍历每一列
                ExcelModel excelModel = new ExcelModel();

                Cell cell1 = row.getCell(0);
                excelModel.setHouseholdNum(String.valueOf(cell1.getStringCellValue()));
                Cell cell2 = row.getCell(1);
                excelModel.setActualAmount(BigDecimal.valueOf(cell2.getNumericCellValue()));
                Cell cell3 = row.getCell(2);
                excelModel.setCarryingAmount(BigDecimal.valueOf(cell3.getNumericCellValue()));
                Cell cell4 = row.getCell(3);
                excelModel.setOwedAmount(BigDecimal.valueOf(cell4.getNumericCellValue()));
                list.add(excelModel);
//                Iterator<Cell> cellIterator = row.cellIterator();
//                while (cellIterator.hasNext()) {
//                    Cell cell = cellIterator.next();
//                    String value;
//                    switch (cell.getCellTypeEnum()) {
//                        case STRING:
//                            value =cell.getStringCellValue();
//                            break;
//                        case NUMERIC:
//                            value = String.valueOf(cell.getNumericCellValue());
//                            break;
//                        default:
//                            // Handle other types if needed
//                            value = "";
//                            break;
//                    }
//                    System.out.print(value + "\t");
//                }
//                System.out.println(); // 打印换行
            }

            for (ExcelModel excelModel : list) {
                String a = "update biz_monthly_carry_forward  set actual_amount = '" + excelModel.getActualAmount()
                        +"' , owed_amount = '" + excelModel.getOwedAmount() + "' where year = 2024 and month = 4 and household_num = '"
                        + excelModel.getHouseholdNum() + "' and carrying_amount = '" +excelModel.getCarryingAmount() + "';";
                System.out.println(a);
            }
            // 关闭工作簿和输入流
            workbook.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
